package com.sihai.ehr.utils;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * @author: wxn
 * @since: 12-7-19 下午10:30
 */
public class ExcelUtils {
    public static List<String[]> readExcel(File file, int sheet, int offsetRow) {
		InputStream is = null;
		FileInputStream fn = null;
		int cs = 0;
		int rs = 0;
		try {
			List list = new ArrayList();
			fn = new FileInputStream(file);
			is = fn;
			Workbook rb = Workbook.getWorkbook(is);
			Sheet s = rb.getSheet(sheet);//此处只读取第一个sheet内容
			cs = s.getColumns();
			rs = s.getRows();
			for (int j = offsetRow; j < rs; j++) {
				String[] row = new String[cs];
				Cell[] c = s.getRow(j);
				for (int x = 0; x < c.length; x++) {
					row[x] = c[x].getContents();

					System.out.print(c[x].getContents() + "\t");
				}
				list.add(row);
				System.out.print("\r\n");
			}
			//System.out.println("Columns:" + cs + "  Rows:" + rs);

			return list;
		} catch (Exception e) {
			System.out.println(e.toString());
		} finally {
			try {
				fn.close();
				is.close();
			} catch (Exception e) {
				System.out.println(e.toString());
			}
		}
		return null;
	}

    /**
     * poi读excel
     * @param file excel文件
     * @param sheet 所读的sheet序号
     * @param offsetRow 从第几行开始读
     * @return
     */
    public static List<String[]> readExcelUsePoi(File file, int sheet, int offsetRow) {
        POIFSFileSystem fs = null;
        HSSFWorkbook wb = null;
        HSSFSheet hssfSheet = null;
        try {
            fs = new POIFSFileSystem(new FileInputStream(file));
            wb = new HSSFWorkbook(fs);

            hssfSheet = wb.getSheetAt(0);
            // 得到总行数
            int rowNum = hssfSheet.getLastRowNum();

            List<String[]> list = new ArrayList<String[]>();

            HSSFRow row = hssfSheet.getRow(sheet);
            //int colNum = row.getPhysicalNumberOfCells();
            int colNum = 78;
            DecimalFormat decimalFormat = new DecimalFormat("#.##");
            for (int i = offsetRow; i <= rowNum; i++) {
                row = hssfSheet.getRow(i);
                int j = 0;
                String[] data = new String[colNum];
                while (j < colNum) {
                    if(row.getCell(j) == null){
                        data[j] = "";
                    }else{
                        switch (row.getCell(j).getCellType()){
                            case HSSFCell.CELL_TYPE_NUMERIC:
                                data[j] = decimalFormat.format(row.getCell(j).getNumericCellValue());
                                break;
                            case HSSFCell.CELL_TYPE_STRING:
                                data[j] = row.getCell(j).getStringCellValue().trim();
                                break;
                            default:
                                data[j] = "";
                                break;
                        }
                    }
                    //System.out.print(data[j] + " ");
                    j++;
                }
                list.add(data);
                //System.out.println("");
            }
            return list;

        } catch (IOException e) {
            e.printStackTrace();
            return null;
        }
    }

    private static void modifyExcel(File file, int sheet){
        try {
            Workbook workbook = Workbook.getWorkbook(file);

            // Workbook 对象是只读的，所以如果要修改Excel，需要创建一个可读的副本，副本指向原Excel文件（即下面的new File(excelpath)）
            //WritableWorkbook writableWorkBook = Workbook.createWorkbook(file, workbook);//创建workbook的副本(副本指向原本)
            WritableWorkbook writableWorkBook = Workbook.createWorkbook(new File("/home/wxn/public/employee_info_template3.xls"), workbook);//创建workbook的副本
            WritableSheet writableSheet = writableWorkBook.getSheet(sheet);//此处只读取第一个sheet内容

            int rowNum = 0;    //要写的行，jxl操作excel时，第一行是从0开始，以此类推

                //WritableCell cell = writableSheet.getWritableCell(70, rowNum+i);//获取第一个单元格
                Label label = new Label(70, rowNum+10, "安了加的1");
                Label label2 = new Label(71, rowNum+12, "安了加的2");
                writableSheet.addCell(label);
                writableSheet.addCell(label2);


            writableWorkBook.write();
            writableWorkBook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private void write(){
        WritableWorkbook workbook = null;

        try {
            workbook = Workbook.createWorkbook(new File("/home/wxn/public/employee_data00.xls"));

            WritableSheet ws=workbook.createSheet("test123",1);

            Label label1=new Label(0,0,"aaa");
            Label label2=new Label(1,0,"bbb");
            Label label3=new Label(2,0,"ccc");

            Label label4=new Label(0,1,"ddd");
            Label label5=new Label(1,1,"eee");
            Label label6=new Label(2,1,"fff");


            try {
                ws.addCell(label1);
                ws.addCell(label2);
                ws.addCell(label3);
                ws.addCell(label4);
                ws.addCell(label5);
                ws.addCell(label6);

                workbook.write();
                workbook.close();
            } catch (Exception e) {
                e.printStackTrace();
            }


        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        //readExcel(new File("/home/wxn/public/employee_info_template2.xls"), 0, 3);
        //modifyExcel(new File("/home/wxn/public/employee_info_template2.xls"), 0);
        //ExcelUtils util = new ExcelUtils();
        //util.write();
        List<String[]> rows = ExcelUtils.readExcelUsePoi(new File("/home/wxn/public/employee_data.xls"), 0, 3);
        System.out.println("wxn------rows");
    }
}
